# -*- coding: utf-8 -*-
import sys
sys.path.append("..")

from MysqlCon import MysqlCon
import time
import xlrd
#import xlwt


class OfficeBB(object):
    """
    写字楼-办办 数据的导入

    1、读取文件数据
    2、写入库表
    """


    #多行插入
    def insert_many(self, connect, cursor, insert_data):
        # 获取数据，写入数据库
        insert_sql = """
        insert into ori_offic_info_bb_20210105(
            platform, url, city_name, county_name, office_name, lnglat, address, rou_location, office_type, office_level, floor_num, building_area, land_area, floor_area, floor_height, volume_rate, property_fee, park_num, day_rent, park_rent, elevator_num, gd_city, gd_county, gd_lnglat, gd_office_name, is_accurate, office_intro
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        connect.ping(reconnect = True)

        # 批量插入
        try:
            res = cursor.executemany(insert_sql, insert_data)
            print("res = ", res)
            # 提交，不进行提交无法保存到数据库
            connect.commit()
        except Exception as e:
            print(e)
            connect.rollback()
        #finally:
        #connect.close()

        return True


if __name__ == "__main__":

    # 获取数据库连接
    mysql_con = MysqlCon()
    connect_odsdb, cursor_odsdb = mysql_con.con_odsdb()

    #记录执行开始时间
    str_time = int(time.time())

    #实例化类
    office = OfficeBB()

    # 读取原文件
    workbook = xlrd.open_workbook(r'D:\\usr\\EJU\\数据部门\\大数据系统\\商业客户数据部\\数据迁移\\待入库文件20210111\\写字楼-办办.xlsx')
    table = workbook.sheet_by_index(0)
    maxRow = table.nrows
    print("总记录数", maxRow)

    insertDataList = []
    for rowNum in range(maxRow):
        #跳过第一行表头
        if rowNum == 0 :
            #跳过第一行表头
            header = table.row_values(rowNum)
            print(header)
            continue
        rowVale = table.row_values(rowNum)
        #print(rowVale)
        insertDataList.append(rowVale)
        if rowNum > 0 and (rowNum % 1000 == 0 or rowNum == maxRow-1):
            print(rowNum)
            # 批量写入1000条数据
            office.insert_many(connect_odsdb, cursor_odsdb, insertDataList)
            insertDataList.clear()
            #break

    connect_odsdb.close()

    end_time = int(time.time())
    print("程序执行时间（秒）：", end_time - str_time)
